"""
Case Type   : USTORE
Case Name   : 验证参数undo_retention_time的生效机制
Create At   : 2025/3/10
Owner       : wan005
Description :
    1.查询参数默认值
    2.修改undo_retention_time值为10
    3.创建ustore表,并插入数据
    4.插入新的数据，闪回查询插入前的数据
    5.10s后，再次闪回查询插入前的数据
Expect      :
    1.参数默认值0
    2.修改成功
    3.创建成功
    4.查看到新插入前的数据
    5.报错时间点已过期
History     :
"""
import os
import time
import unittest
import sys
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant


class Security(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'---{os.path.basename(__file__)} start---')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table = 't_ustore_01'

    def test_ustore(self):
        text = '-----pre1:参数enable_recyclebin设置为on-----'
        self.logger.info(text)
        pre_res1 = self.sh_primy.execute_gsguc('reload',
                self.constant.GSGUC_SUCCESS_MSG, 'enable_recyclebin=on')
        self.assertEqual(True, pre_res1, text + '执行失败')

        text = '-----step1:查询参数默认值;expect:参数默认值0-----'
        self.logger.info(text)
        res1 = self.common.show_param('undo_retention_time')
        self.assertEqual('0', res1, text + '执行失败')

        text = '----step2:修改参数值为10;expect:成功----'
        self.logger.info(text)
        self.sh_primy.execute_gsguc('reload',
            self.constant.GSGUC_SUCCESS_MSG, 'undo_retention_time=10')
        res2 = self.common.show_param('undo_retention_time')
        self.assertEqual('10s', res2, text + '执行失败')

        text = '-----step3:创建ustore表;expect:创建成功-----'
        self.logger.info(text)
        sql3 = f'drop table if exists {self.table};' \
               f'create table {self.table}(age int primary key) ' \
                   f'with (storage_type=ustore);' \
               f'insert into {self.table} values(5);'
        res3 = self.sh_primy.execut_db_sql(sql3)
        self.logger.info(res3)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res3, text + '执行失败')
        time.sleep(4)
        date_sql = f'select sysdate;'
        date_msg = self.sh_primy.execut_db_sql(date_sql)
        self.logger.info(date_msg)
        sysdate = date_msg.splitlines()[2].strip()
        self.logger.info(sysdate)

        text = '---step4:插入新的数据，闪回查询插入前的数据;expect:查看到新插入前的数据---'
        self.logger.info(text)
        sql4_1 = f'insert into {self.table} values(3);'
        res4_1 = self.sh_primy.execut_db_sql(sql4_1)
        self.logger.info(res4_1)
        self.assertEqual('INSERT 0 1', res4_1, text + '执行失败')
        time.sleep(3)
        sql4_2 = f'select * from {self.table} timecapsule timestamp ' \
                 f'to_timestamp (\'{sysdate}\', \'YYYY-MM-DD HH24:MI:SS\');'
        self.logger.info(sql4_2)
        res4_2 = self.sh_primy.execut_db_sql(sql4_2)
        self.logger.info(res4_2)
        self.common.equal_sql_mdg(res4_2, 'age', '5', '(1 row)', flag='1')

        text = '-----step5:10s后，再次闪回查询插入前的数据;expect:失败-----'
        self.logger.info(text)
        time.sleep(10)
        sql5 = f'select * from {self.table} timecapsule timestamp ' \
                 f'to_timestamp (\'{sysdate}\', \'YYYY-MM-DD HH24:MI:SS\');'
        res5 = self.sh_primy.execut_db_sql(sql5)
        self.logger.info(res5)
        self.assertIn('timecapsule time is too old', res5, text + '执行失败')

    def tearDown(self):
        text = '-----step1:清理环境，恢复配置;expect:成功-----'
        self.logger.info(text)
        self.sh_primy.execute_gsguc('reload', self.constant.GSGUC_SUCCESS_MSG,
                                    'undo_retention_time=0')
        res1 = self.common.show_param('undo_retention_time')
        self.assertEqual('0', res1, text + '执行失败')
        self.sh_primy.execute_gsguc('reload', self.constant.GSGUC_SUCCESS_MSG,
                                    'enable_recyclebin=off')
        res2 = self.common.show_param('enable_recyclebin')
        self.assertEqual('off', res2, text + '执行失败')
        sql_cmd3 = f'drop table if exists {self.table};'
        res3 = self.sh_primy.execut_db_sql(sql_cmd3)
        self.logger.info(res3)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res3,
                         text + '执行失败')
        self.logger.info(f'---{os.path.basename(__file__)} end---')
